SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO











CREATE PROC [dbo].[SP_TMPLAN_EMPR_Q01]
/*-------------------------------------------------------------------*/
/*----- Empresa           : OFISIS S.A.                          ----*/
/*----- Cliente           : OFISIS S.A.                          ----*/
/*----- Sistema           : Contabilidad                         ----*/
/*----- Modulo            : Administracion                       ----*/
/*----- Programa          : Validacion del Plan de Cuentas       ----*/
/*----- Nombre            : SP_TMPLAN_EMPR_Q01                   ----*/
/*----- Script            : TMPLAN_EMPR                          ----*/
/*----- Desarrollado por  : Martin Garcia Farfan                 ----*/
/*----- Fecha             : 10/02/1999                           ----*/
/*----- Base de Datos     : MS SQL Server                        ----*/
/*----- Version BD        : 6.5                                  ----*/
/*-------------------------------------------------------------------*/
/*-------------------------------------------------------------------*/
/*----- Drop proc SP_TMPLAN_EMPR_Q01                             ----*/
/*----- Grant all on SP_TMPLAN_EMPR_Q01 to PUBLIC                ----*/
/*----- SP_TMPLAN_EMPR_Q01 'SS', 1, 1999, 0, 2                   ----*/
/*-------------------------------------------------------------------*/
@ISCO_EMPR       TD_VC_002,
@INNU_CNTB_EMPR  TD_IN_001,
@INNU_ANNO       TD_IN_004_ANNO,
@INNU_MESE_INIC  TD_IN_002_MESE,
@INNU_MESE_FINA  TD_IN_002_MESE,
@ISTI_REPO  	 TD_VC_001 = NULL

as

/*-- NO DEBEN LLEVAR AUXILIAR Y TIENEN --*/
IF @ISTI_REPO = 'D'
 BEGIN
 select '1', 'NO DEBEN LLEVAR AUXILIAR Y TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, T1.NU_MESE , CONVERT( VARCHAR(2), T1.NU_MESE )+ ' :               ' +  T1.CO_UNID_CNTB + ' - ' + T1.CO_OPRC_CNTB + ' - ' + T1.NU_ASTO , convert (varchar ,T1.FE_ASTO_CNTB,
103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.TI_AUXI_EMPR IS NOT NULL
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_AUXI_EMPR = 'N'
 and t2.NU_ANNO = 9999
-- group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 union all
/*-- DEBEN LLEVAR AUXILIAR Y NO TIENEN --*/
 select '2', 'DEBEN LLEVAR AUXILIAR Y NO TIENEN', t1.CO_CNTA_EMPR, t2.DE_CNTA_EMPR, T1.NU_MESE , CONVERT( VARCHAR(2), T1.NU_MESE )+ ' :               ' +  T1.CO_UNID_CNTB + ' - ' + T1.CO_OPRC_CNTB + ' - ' + T1.NU_ASTO, convert (varchar ,T1.FE_ASTO_CNTB,103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and (t1.TI_AUXI_EMPR IS NULL or t1.CO_AUXI_EMPR IS NULL)
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_AUXI_EMPR = 'S'
 and t2.NU_ANNO = 9999
-- group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 union all
/*-- NO DEBEN LLEVAR DOCUMENTO Y TIENEN --*/
 select '3', 'NO DEBEN LLEVAR DOCUMENTO Y TIENEN', t1.CO_CNTA_EMPR, t2.DE_CNTA_EMPR, T1.NU_MESE , CONVERT( VARCHAR(2), T1.NU_MESE )+ ' :               ' +  T1.CO_UNID_CNTB + ' - ' + T1.CO_OPRC_CNTB + ' - ' + T1.NU_ASTO , convert (varchar ,T1.FE_ASTO_CNTB,103), 
        @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR  and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.TI_DOCU IS NOT NULL
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_DOCU_CNTB = 'N'
 And T2.NU_ANNO = 9999
 -- group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
-- and t2.NU_ANNO = @INNU_ANNO
 union all
/*-- DEBEN LLEVAR DOCUMENTO Y NO TIENEN --*/
 select '4', 'DEBEN LLEVAR DOCUMENTO Y NO TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, T1.NU_MESE , CONVERT( VARCHAR(2), T1.NU_MESE )+ ' :               ' +  T1.CO_UNID_CNTB + ' - ' + T1.CO_OPRC_CNTB + ' - ' + T1.NU_ASTO , convert (varchar ,T1.FE_ASTO_CNTB
,103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.TI_DOCU IS null
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_DOCU_CNTB = 'S'
 and t2.NU_ANNO = 9999
 -- group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 UNION ALL
 select '5', 'DEBEN LLEVAR ORDEN DE SERVICIO Y NO TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, T1.NU_MESE , CONVERT( VARCHAR(2), T1.NU_MESE )+ ' :               ' +  T1.CO_UNID_CNTB + ' - ' + T1.CO_OPRC_CNTB + ' - ' + T1.NU_ASTO , convert (varchar ,T1.FE_ASTO_CNTB,103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.SI_MVTO_CNTB = 'APR'
 and DATALENGTH(ISNULL(T1.CO_ORDE_SERV,'')) = 0
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and T2.ST_ORDE_SERV = 'S'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 UNION ALL
 select '6', 'NO DEBEN LLEVAR ORDEN DE SERVICIO Y TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, T1.NU_MESE ,CONVERT( VARCHAR(2), T1.NU_MESE )+ ' :               ' +  T1.CO_UNID_CNTB + ' - ' + T1.CO_OPRC_CNTB + ' - ' + T1.NU_ASTO , convert (varchar ,T1.FE_ASTO_CNTB,103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.SI_MVTO_CNTB = 'APR'
 and T1.CO_ORDE_SERV IS NOT NULL
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and T2.ST_ORDE_SERV = 'N'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 order by 1, 2 , 5, 6
END
ELSE
BEGIN
/*-- NO DEBEN LLEVAR AUXILIAR Y TIENEN --*/
 select '1', 'NO DEBEN LLEVAR AUXILIAR Y TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, COUNT(*), '' ,CONVERT(Varchar ,GETDATE(),103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.TI_AUXI_EMPR IS NOT NULL
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_AUXI_EMPR = 'N'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 union all
/*-- DEBEN LLEVAR AUXILIAR Y NO TIENEN --*/
 select '2', 'DEBEN LLEVAR AUXILIAR Y NO TIENEN', t1.CO_CNTA_EMPR, t2.DE_CNTA_EMPR, COUNT(*), '' ,CONVERT(Varchar ,GETDATE(),103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and (t1.TI_AUXI_EMPR IS NULL or t1.CO_AUXI_EMPR IS NULL)
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_AUXI_EMPR = 'S'
 and t1.SI_MVTO_CNTB = 'APR'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 union all
/*-- NO DEBEN LLEVAR DOCUMENTO Y TIENEN --*/
 select '3', 'NO DEBEN LLEVAR DOCUMENTO Y TIENEN', t1.CO_CNTA_EMPR, t2.DE_CNTA_EMPR, COUNT(*), '' ,CONVERT(Varchar ,GETDATE(),103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR  and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.TI_DOCU IS NOT NULL
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_DOCU_CNTB = 'N'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 union all
/*-- DEBEN LLEVAR DOCUMENTO Y NO TIENEN --*/
 select '4', 'DEBEN LLEVAR DOCUMENTO Y NO TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, COUNT(*), '' ,CONVERT(Varchar ,GETDATE(),103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.TI_DOCU IS null
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and t2.ST_DOCU_CNTB = 'S'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 UNION ALL
 select '5', 'DEBEN LLEVAR ORDEN DE SERVICIO Y NO TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, COUNT(*), '' ,CONVERT(Varchar ,GETDATE(),103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.SI_MVTO_CNTB = 'APR'
 and DATALENGTH(ISNULL(T1.CO_ORDE_SERV,'')) = 0
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and T2.ST_ORDE_SERV = 'S'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 UNION ALL
 select '6', 'NO DEBEN LLEVAR ORDEN DE SERVICIO Y TIENEN', t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR, COUNT(*), '' ,CONVERT(Varchar ,GETDATE(),103), @ISTI_REPO
 from TXMVTO_CNTB t1, TMPLAN_EMPR t2
 where t1.CO_EMPR = @ISCO_EMPR
 and t1.NU_CNTB_EMPR = @INNU_CNTB_EMPR
 and t1.NU_ANNO = @INNU_ANNO
 and t1.NU_MESE >= @INNU_MESE_INIC and t1.NU_MESE <= @INNU_MESE_FINA
 and t1.SI_MVTO_CNTB = 'APR'
 and t1.CO_EMPR = t2.CO_EMPR
 and t1.CO_CNTA_EMPR = t2.CO_CNTA_EMPR
 and T1.CO_ORDE_SERV IS NOT NULL
 and T2.ST_ORDE_SERV = 'N'
 and t2.NU_ANNO = 9999
-- and t2.NU_ANNO = @INNU_ANNO
 group by t1.CO_CNTA_EMPR , t2.DE_CNTA_EMPR
 order by 1, 2
END
return
/*------------------------------------------- Fin ------------------------------------*/















GO
